This invention relates generally to processing data and, more particularly, to systems and methods for calculating and retrieving analytic data using a body of transactional data managed by a relational database management system (RDBMS).
Transactional data, such as payment card transaction data, is collected and processed routinely by organizations associated with the transactions. Payment card transactions (e.g., credit cards, debit cards, pre-paid cards, gift cards, etc.) may involve a number of parties, including merchants, issuers, cardholders, acquirers, and payment networks. Payment card transactions are so ubiquitous in society today that the volume of data associated with these transactions has grown quite large. The entities tasked with tracking, maintaining, and reporting on these transactions must efficiently process large numbers of transactions daily.
In some cases, interchange networks act as central communication hubs for processing transactional data, providing services to at least some of the parties involved in the payment card transaction. Routinely, payment networks are asked to provide reporting to the customers that they support. For example, an issuer may request that an interchange network provide the issuer with totals from a prior day's transactions. To provide such a service, an analyst associated with the interchange network traditionally must query a large database and generate an aggregated result to return to the analyst. Based on a variety of variables, such as system load, database size, the type of query, and indexing efficiency, the analyst's query may take many seconds or minutes to complete.
Some known systems for increasing efficiency of these aggregation calculations involve creating separate tables of data, sometimes called “aggregate tables.” These aggregate tables may store duplicate information, allowing a query to run on a subset of the data rather than a larger database. Other aggregate tables may compute a specific aggregation for each customer and store these values in a separate table. These approaches improve efficiency in certain situations by decreasing the amount of data to search, indexing on a key field tuned for a particular query, or pre-calculating a value. However, each of these approaches requires custom data structures to be built and maintained.
Accordingly, a method and system is needed that enables the payment network to: (i) store pre-calculated aggregation results in a database structure that allows the flexibility to field different types of queries without unnecessarily duplicating data; (ii) structure the database and queries quickly identify and provided the results to a requesting customer; and (iii) facilitate periodic updating of the aggregation results.